package uk.ac.cam.echo.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;

import uk.ac.cam.echo.ScheduleEvent;
import uk.ac.cam.echo.messages.CheckInMessage;
import uk.ac.cam.echo.messages.LocationMessage;
import uk.ac.cam.echo.messages.Message;
import uk.ac.cam.echo.messages.SecurityMessage;
import uk.ac.cam.echo.messages.UserMessage;

/**
 * @author Tom Smith 
 */
public class Database {

	private static Connection m_Connection;
	private static Database m_Database;

	public static final String tblAttraction = "Attraction";
	public static final String tblCheckIn = "CheckIn";
	// Entity names for database.	
	public static final String tblFriend = "Friend";
	public static final String tblLocation = "Location";
	public static final String tblLocationCheckIn = "LocationCheckIn";
	public static final String tblSchedule = "Schedule";
	public static final String tblUser = "User";

	/**
	 * Closes the JDBC connection.
	 * @throws SQLException
	 */
	public static void closeConnection() throws SQLException {
		if (Database.m_Connection != null && !Database.m_Connection.isClosed())
			Database.m_Connection.close();
	}

	/**
	 * Return the instance of the Database.
	 * @return The instance of the Database.
	 */
	public static Database getInstance() {
		return Database.m_Database;
	}

	/**
	 * Sets the connection for the Database, if it does not already exist.
	 * @param conn A connection to give the database, if it has not already been set.
	 * @return The instance of the Database.
	 * @throws SQLException
	 */
	public static Database setConnection(Connection conn) throws SQLException {
		if (Database.m_Database == null) {
			Database.m_Database = new Database(conn);
		}
		return Database.m_Database;
	}

	/**
	 * Create a new Database object for the connection.
	 * @param conn The JDBC connection to use.
	 * @throws SQLException
	 */
	private Database(Connection conn) throws SQLException {
		Database.m_Connection = conn;
		Database.m_Connection.setAutoCommit(false);
	}

	/**
	 * Adds a record into the Friend table indicating fromUserID is a friend to toUserID.
	 * @param fromUserID the user sending the request.
	 * @param toUserID the user being requested.
	 * @throws SQLException
	 */
	public synchronized void addFriendInstance(String fromUserID, String toUserID) throws SQLException {
		if (Database.m_Connection == null) return;

		String insert = "INSERT INTO " + Database.tblFriend + " VALUES(?,?);";

		PreparedStatement preparedStatement = Database.m_Connection.prepareStatement(insert);
		preparedStatement.setString(1, fromUserID);
		preparedStatement.setString(2, toUserID);

		try {
			preparedStatement.executeUpdate();
		} catch (SQLIntegrityConstraintViolationException e) {
			// Most likely because the friend has already been added, so we don't need to do anything.
		}

		preparedStatement.close();


		Database.m_Connection.commit();
	}

	/**
	 * Checks if there is already an entry from this user to another user.
	 * @param fromUserID the user sending the friend request.
	 * @param toUserID the user it is being sent to.
	 * @return Whether or not the entry already exists.
	 * @throws SQLException
	 */
	public synchronized boolean areFriends(String fromUserID, String toUserID) throws SQLException {
		if (Database.m_Connection == null) return false;

		String query = "SELECT toUserID AS fID FROM " + Database.tblFriend + " WHERE fromUserID=? AND toUserID=? " +
				"INTERSECT SELECT fromUserID AS fID FROM " + Database.tblFriend + " WHERE toUserID=? AND fromUserID=?;";

		PreparedStatement preparedStatement = Database.m_Connection.prepareStatement(query);
		preparedStatement.setString(1, fromUserID);
		preparedStatement.setString(2, toUserID);
		preparedStatement.setString(3, fromUserID);
		preparedStatement.setString(4, toUserID);

		ResultSet results = preparedStatement.executeQuery();

		boolean friends = false;

		if (results.next()) {
			friends = (results.getString(1).equals(toUserID));
		}

		results.close();
		preparedStatement.close();

		Database.m_Connection.commit();

		return friends;
	}

	/**
	 * Checks in the user at the specified attraction.
	 * @param userRFID The user RFID.
	 * @param attractionID The attraction ID to check in that.
	 * @param time The time of the check in.
	 * @throws SQLException
	 */
	public synchronized void checkIn(String userRFID, int attractionID, long time) throws SQLException {

		if (Database.m_Connection == null) return;

		String query = "INSERT INTO " + Database.tblCheckIn + " VALUES(?,?,?);";

		PreparedStatement preparedStatement = Database.m_Connection.prepareStatement(query);
		preparedStatement.setString(1, userRFID);
		preparedStatement.setInt(2, attractionID);
		preparedStatement.setTimestamp(3, new Timestamp(time));
		try {
			preparedStatement.executeUpdate();
		} catch (SQLIntegrityConstraintViolationException e) {
		}
	}

	/**
	 * Given an RFID, return a UserMessage if it is a user, or a LocationMessage if it is a Location.
	 * @param rfid The RFID scanned.
	 * @return A Message relating to this RFID. NULL if neither of them.
	 * @throws SQLException
	 */
	public synchronized Message checkRFID(String rfid) throws SQLException {

		if (Database.m_Connection == null) return null;

		Message message = null;

		String userQuery = "SELECT Forename, Surname, UserType, FacebookID FROM " + Database.tblUser + " WHERE RFID=?;";
		String locationQuery = "SELECT LocationName FROM " + Database.tblLocation + " WHERE LocationRFID=?;";

		// If the RFID is for a User.
		PreparedStatement preparedStatement = Database.m_Connection.prepareStatement(userQuery);
		preparedStatement.setString(1, rfid);

		ResultSet results = preparedStatement.executeQuery();


		if (results.next()) {
			message = new UserMessage(rfid);
			((UserMessage) message).setForename(results.getString(1));
			((UserMessage) message).setSurname(results.getString(2));
			((UserMessage) message).setFacebookID(results.getString(4));

			results.close();
			preparedStatement.close();

			// Commit changes to file, and release DB locks.
			Database.m_Connection.commit();

			return message;

		} 

		// If the RFID is for a Location.
		preparedStatement = Database.m_Connection.prepareStatement(locationQuery);
		preparedStatement.setString(1, rfid);

		results = preparedStatement.executeQuery();

		if (results.next()) {
			message = new LocationMessage(rfid, results.getString(1));

			results.close();
			preparedStatement.close();

			// Commit changes to file, and release DB locks.
			Database.m_Connection.commit();

			return message;

		} 

		results.close();
		preparedStatement.close();

		return new Message(rfid);
	}

	/**
	 * Get a list of check-ins for the user RFID.
	 * @param userRFID The user in question.
	 * @return A list containing check-ins.
	 * @throws SQLException 
	 */
	public synchronized List<CheckInMessage> getCheckIns(String userRFID) throws SQLException {
		if (Database.m_Connection == null) return null;

		String query = "SELECT " + Database.tblAttraction + ".AttractionID, " + Database.tblAttraction + ".AttractionName, " + Database.tblCheckIn + ".Time " +
				"FROM " + Database.tblCheckIn + " INNER JOIN " + Database.tblAttraction + " " +
				"ON " + Database.tblAttraction + ".AttractionID = " + Database.tblCheckIn + ".AttractionID " +
				"WHERE " + Database.tblCheckIn + ".RFID = ?;";

		PreparedStatement preparedStatement = Database.m_Connection.prepareStatement(query);
		preparedStatement.setString(1, userRFID);

		// Get check-ins for this user.
		ResultSet results = preparedStatement.executeQuery();

		// Loop through ResultSet, adding all RFIDs to list.
		List<CheckInMessage> checkIns = new LinkedList<CheckInMessage>();
		while (results.next()) {

			long time = 0;
			Timestamp timestamp = results.getTimestamp(3);
			if (timestamp != null) time = timestamp.getTime();

			checkIns.add(new CheckInMessage(userRFID, results.getInt(1), results.getString(2), time));

		}

		results.close();
		preparedStatement.close();

		// Commit changes to file, and release DB locks.
		Database.m_Connection.commit();

		return checkIns;
	}

	/**
	 * Get a list of the user's friends' IDs.
	 * @param userRFID the user's RFID.
	 * @return List containing friends' IDs.
	 * @throws SQLException If an error occurs in the query.
	 */
	public synchronized List<String> getFriendID(String userRFID) throws SQLException {
		if (Database.m_Connection == null) return null;

		String query = "SELECT toUserID AS fID FROM " + Database.tblFriend + " WHERE fromUserId = ? " +
				"INTERSECT SELECT fromUserID AS fID FROM " + Database.tblFriend + " WHERE toUserID = ?;";

		PreparedStatement preparedStatement = Database.m_Connection.prepareStatement(query);
		preparedStatement.setString(1, userRFID);
		preparedStatement.setString(2, userRFID);

		// Gets IDs of people where you are friend of them, and they are friend of you.
		ResultSet results = preparedStatement.executeQuery();

		// Loop through ResultSet, adding IDs of all friends to the list.
		List<String> friends = new LinkedList<String>();
		while (results.next()) {
			friends.add(results.getString(1));
		}
		results.close();
		preparedStatement.close();

		// Commit changes to file, and release DB locks.
		Database.m_Connection.commit();

		return friends;

	}

	/**
	 * Return a list of people who have requested friendship with this user.
	 * @param userRFID The user we want requests for.
	 * @return A list of users who want to be friends.
	 * @throws SQLException
	 */
	public synchronized List<String> getFriendRequests(String userRFID) throws SQLException {
		if (Database.m_Connection == null) return null;

		String query = "SELECT fromUserID AS fID FROM " + Database.tblFriend + " WHERE toUserID = ? " +
				"MINUS SELECT toUserID AS fID FROM " + Database.tblFriend + " WHERE fromUserID = ?;";

		PreparedStatement preparedStatement = Database.m_Connection.prepareStatement(query);
		preparedStatement.setString(1, userRFID);
		preparedStatement.setString(2, userRFID);

		// Gets the users which have added this person, but not been added.
		ResultSet results = preparedStatement.executeQuery();

		// Loop through ResultSet, adding all RFIDs to list.
		List<String> requests = new LinkedList<String>();
		while (results.next()) {			
			requests.add(results.getString(1));
		}
		results.close();
		preparedStatement.close();

		// Commit changes to file, and release DB locks.
		Database.m_Connection.commit();

		return requests;
	}
	
	/**
	 * Get a list of all of the locations in the database.
	 * @return list of LocationMessages, one for each location.
	 * @throws SQLException
	 */
	public synchronized List<LocationMessage> getLocations() throws SQLException {
		if (Database.m_Connection == null) return null;

		String query = "SELECT LocationRFID, LocationName FROM " + Database.tblLocation + ";";

		PreparedStatement preparedStatement = Database.m_Connection.prepareStatement(query);

		// Gets Security details of the corresponding RFID.
		ResultSet results = preparedStatement.executeQuery();

		List<LocationMessage> locationList = new LinkedList<LocationMessage>();
		
		while (results.next()) {
			LocationMessage location = new LocationMessage(results.getString(1), results.getString(2));
			
			locationList.add(location);
		}

		results.close();
		preparedStatement.close();

		// Commit changes to file, and release DB locks.
		Database.m_Connection.commit();

		return locationList;
	}

	/**
	 * Gets the next events which have not yet started.
	 * @param Number of events in the future to get.
	 * @return List containing the events.
	 * @throws SQLException
	 */
	public synchronized List<ScheduleEvent> getSchedule(int amount, String locationRFID) throws SQLException {
		if (Database.m_Connection == null) return null;

		String queryWithLocation = "SELECT EventName, StartTime, EndTime, Description FROM " + Database.tblSchedule + " " +
				"INNER JOIN " + Database.tblAttraction + " " +
				"ON " + Database.tblSchedule + ".AttractionID = " + Database.tblAttraction + ".AttractionID " +
				"WHERE StartTime >= NOW() AND " + Database.tblAttraction + ".LocationRFID = ? " +
				"ORDER BY StartTime " +
				"LIMIT 0, ?;";

		String queryWithoutLocation = "SELECT EventName, StartTime, EndTime, Description FROM " + Database.tblSchedule + " " +
				"WHERE StartTime >= NOW() " +
				"ORDER BY StartTime " +
				"LIMIT 0, ?;";

		PreparedStatement preparedStatement = null;
		ResultSet results = null;

		if (locationRFID == null) {
			// Gets the next /amount/ events which start now or later.
			preparedStatement = Database.m_Connection.prepareStatement(queryWithoutLocation);
			preparedStatement.setInt(1, amount);
			results = preparedStatement.executeQuery();

		} else {
			// Get the next /amount/ events which start now or later for the locationRFID provided.
			preparedStatement = Database.m_Connection.prepareStatement(queryWithLocation);
			preparedStatement.setString(1, locationRFID);
			preparedStatement.setInt(2, amount);
			results = preparedStatement.executeQuery();
		}

		// Loop through ResultSet, adding all events to list.
		List<ScheduleEvent> events = new LinkedList<ScheduleEvent>();
		while (results.next()) {

			long start = 0;
			Timestamp startTime = results.getTimestamp(2);
			if (startTime != null) {
				start = startTime.getTime();
			}

			long end = 0;
			Timestamp endTime = results.getTimestamp(3);
			if (endTime != null) {
				end = endTime.getTime();
			}

			events.add(new ScheduleEvent(results.getString(1), results.getString(4), start, end));
		}
		results.close();
		preparedStatement.close();

		// Commit changes to file, and release DB locks.
		Database.m_Connection.commit();

		return events;
	}

	/**
	 * Get the user details with security details for this user.
	 * @return A SecurityMessage containing details for this user.
	 * @throws SQLException If an error occurs in the query.
	 */
	public synchronized SecurityMessage getSecurityDetails(String rfid) throws SQLException {
		if (Database.m_Connection == null) return null;

		String query = "SELECT Forename, Surname, UserType, FacebookID FROM " + Database.tblUser + " WHERE RFID = ?;";

		PreparedStatement preparedStatement = Database.m_Connection.prepareStatement(query);
		preparedStatement.setString(1, rfid);

		// Gets Security details of the corresponding RFID.
		ResultSet results = preparedStatement.executeQuery();

		SecurityMessage user = new SecurityMessage(rfid);
		if (results.next()) {
			user.setForename(results.getString(1));
			user.setSurname(results.getString(2));
			user.setUserType(results.getString(3));
			user.setFacebookID(results.getString(4));

			user.setCheckIns(getCheckIns(rfid));
		}

		results.close();
		preparedStatement.close();

		// Commit changes to file, and release DB locks.
		Database.m_Connection.commit();

		return user;
	}

	/**
	 * Get the user details for this user.
	 * @return A UserMessage containing details for this user.
	 * @throws SQLException If an error occurs in the query.
	 */
	public synchronized UserMessage getUserDetails(String rfid) throws SQLException {
		if (Database.m_Connection == null) return null;

		Statement sqlStatement = Database.m_Connection.createStatement();

		// Gets IDs of people where you are friend of them, and they are friend of you.
		ResultSet results = sqlStatement.executeQuery("SELECT Forename,Surname,FacebookID FROM " + Database.tblUser + " " +
				"WHERE RFID='" + rfid + "';");

		UserMessage user = new UserMessage(rfid);
		if (results.next()) {
			user.setForename(results.getString(1));
			user.setSurname(results.getString(2));
			user.setFacebookID(results.getString(3));
		}

		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		Database.m_Connection.commit();

		return user;
	}
	
	/**
	 * Get a list of all of the users in the database.
	 * @return list of UserMessages, one for each user.
	 * @throws SQLException
	 */
	public synchronized List<UserMessage> getUsers() throws SQLException {
		if (Database.m_Connection == null) return null;

		String query = "SELECT RFID, Forename, Surname FROM " + Database.tblUser + ";";

		PreparedStatement preparedStatement = Database.m_Connection.prepareStatement(query);

		// Gets Security details of the corresponding RFID.
		ResultSet results = preparedStatement.executeQuery();

		List<UserMessage> usersList = new LinkedList<UserMessage>();
		
		while (results.next()) {
			UserMessage user = new UserMessage(results.getString(1));
			user.setForename(results.getString(2));
			user.setSurname(results.getString(3));
			
			usersList.add(user);
		}

		results.close();
		preparedStatement.close();

		// Commit changes to file, and release DB locks.
		Database.m_Connection.commit();

		return usersList;
	}

	/**
	 * Sets the user's FacebookID on initial login.
	 * @param userRFID The user's RFID.
	 * @param facebookID The user's Facebook ID.
	 * @throws SQLException
	 */
	public synchronized void setFacebookID(String userRFID, String facebookID) throws SQLException {
		if (Database.m_Connection == null) return;

		Statement sqlStatement = Database.m_Connection.createStatement();
		sqlStatement.execute("UPDATE " + Database.tblUser + " " +
				"SET FacebookID = '" + facebookID + "' " +
				"WHERE RFID = '" + userRFID + "';");
		sqlStatement.close();

		Database.m_Connection.commit();
	}

}
